'use strict';

async function addDeletedAtColumns() {
  try {
    console.log('开始添加缺失的deleted_at字段...');
    
    const { Sequelize } = require('sequelize');
    
    const dbConfig = {
      host: process.env.MySqlHost || '127.0.0.1',
      port: process.env.MySqlPort || 3306,
      user: process.env.MySqlUsername || 'root',
      password: process.env.MySqlPassword || '302108jw302',
    };
    
    const dbName = process.env.MySqlDatabase || 'wardrobe_db';
    
    const sequelize = new Sequelize(dbName, dbConfig.user, dbConfig.password, {
      host: dbConfig.host,
      port: dbConfig.port,
      dialect: 'mysql',
      logging: console.log,
      define: {
        raw: true,
        underscored: true,
        charset: 'utf8mb4',
        timestamps: true,
        paranoid: true,
        freezeTableName: true,
        createdAt: 'created_at',
        updatedAt: 'updated_at',
        deletedAt: 'deleted_at',
      },
      dialectOptions: {
        dateStrings: true,
        typeCast: true,
        charset: 'utf8mb4',
        collate: 'utf8mb4_unicode_ci',
      },
      timezone: '+08:00',
    });
    
    await sequelize.authenticate();
    console.log('数据库连接成功');
    
    // 需要添加deleted_at字段的表
    const tables = [
      'categories',
      'clothing', 
      'outfits',
      'tags',
      'wear_records'
    ];
    
    for (const tableName of tables) {
      try {
        // 检查字段是否已存在
        const [columns] = await sequelize.query(`SHOW COLUMNS FROM ${tableName} LIKE 'deleted_at'`);
        
        if (columns.length === 0) {
          console.log(`为表 ${tableName} 添加 deleted_at 字段...`);
          await sequelize.query(`ALTER TABLE ${tableName} ADD COLUMN deleted_at DATETIME NULL COMMENT '软删除时间戳'`);
          console.log(`✓ 表 ${tableName} 的 deleted_at 字段添加成功`);
        } else {
          console.log(`✓ 表 ${tableName} 已有 deleted_at 字段，跳过`);
        }
      } catch (error) {
        console.error(`✗ 为表 ${tableName} 添加 deleted_at 字段失败:`, error.message);
      }
    }
    
    // 检查关联表（这些表通常不需要软删除）
    const relationTables = ['clothing_tags', 'outfit_items'];
    
    for (const tableName of relationTables) {
      try {
        const [columns] = await sequelize.query(`SHOW COLUMNS FROM ${tableName} LIKE 'deleted_at'`);
        
        if (columns.length === 0) {
          console.log(`关联表 ${tableName} 不需要 deleted_at 字段，跳过`);
        } else {
          console.log(`✓ 关联表 ${tableName} 已有 deleted_at 字段`);
        }
      } catch (error) {
        console.log(`关联表 ${tableName} 检查失败，可能不存在:`, error.message);
      }
    }
    
    await sequelize.close();
    console.log('deleted_at 字段添加完成！');
    
  } catch (error) {
    console.error('添加 deleted_at 字段失败:', error);
    process.exit(1);
  }
}

// 如果直接运行此文件
if (require.main === module) {
  addDeletedAtColumns();
}

module.exports = addDeletedAtColumns;
